from typing import List, Optional
from uuid import UUID
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy import select, func, update, delete
from sqlalchemy.orm import selectinload

from models.document.document_category import DocumentCategory
from models.document.personal_document import PersonalDocument
from schemas.document.document_category import DocumentCategoryCreate, DocumentCategoryUpdate


async def get_category_by_id(db: AsyncSession, category_id: UUID) -> Optional[DocumentCategory]:
    """根据ID获取分类"""
    result = await db.execute(
        select(DocumentCategory).where(DocumentCategory.id == category_id)
    )
    return result.scalar_one_or_none()


async def get_all_categories(db: AsyncSession) -> List[DocumentCategory]:
    """获取所有分类（包含子分类关系）"""
    result = await db.execute(
        select(DocumentCategory)
        .options(selectinload(DocumentCategory.children))
        .order_by(DocumentCategory.sort_order, DocumentCategory.name)
    )
    return result.scalars().all()


async def get_root_categories(db: AsyncSession) -> List[DocumentCategory]:
    """获取根分类（父分类为空的分类）"""
    result = await db.execute(
        select(DocumentCategory)
        .where(DocumentCategory.parent_id.is_(None))
        .options(selectinload(DocumentCategory.children))
        .order_by(DocumentCategory.sort_order, DocumentCategory.name)
    )
    return result.scalars().all()


async def get_categories_with_document_count(db: AsyncSession) -> List[tuple]:
    """获取分类及其文档数量"""
    result = await db.execute(
        select(
            DocumentCategory,
            func.count(PersonalDocument.id).label('document_count')
        )
        .outerjoin(PersonalDocument)
        .group_by(DocumentCategory.id)
        .order_by(DocumentCategory.sort_order, DocumentCategory.name)
    )
    return result.all()


async def create_category(db: AsyncSession, category: DocumentCategoryCreate) -> DocumentCategory:
    """创建新分类"""
    db_category = DocumentCategory(**category.model_dump())
    db.add(db_category)
    await db.commit()
    await db.refresh(db_category)
    return db_category


async def update_category(db: AsyncSession, category_id: UUID, category: DocumentCategoryUpdate) -> Optional[DocumentCategory]:
    """更新分类信息"""
    # 检查分类是否存在
    db_category = await get_category_by_id(db, category_id)
    if not db_category:
        return None
    
    # 更新字段
    update_data = category.model_dump(exclude_unset=True)
    if update_data:
        await db.execute(
            update(DocumentCategory)
            .where(DocumentCategory.id == category_id)
            .values(**update_data)
        )
        await db.commit()
        await db.refresh(db_category)
    
    return db_category


async def delete_category(db: AsyncSession, category_id: UUID) -> bool:
    """删除分类"""
    result = await db.execute(
        delete(DocumentCategory).where(DocumentCategory.id == category_id)
    )
    await db.commit()
    return result.rowcount > 0


async def move_documents_to_category(db: AsyncSession, from_category_id: UUID, to_category_id: Optional[UUID]) -> int:
    """将文档从一个分类移动到另一个分类"""
    result = await db.execute(
        update(PersonalDocument)
        .where(PersonalDocument.category_id == from_category_id)
        .values(category_id=to_category_id)
    )
    await db.commit()
    return result.rowcount


async def get_category_children(db: AsyncSession, parent_id: UUID) -> List[DocumentCategory]:
    """获取指定分类的所有子分类"""
    result = await db.execute(
        select(DocumentCategory)
        .where(DocumentCategory.parent_id == parent_id)
        .order_by(DocumentCategory.sort_order, DocumentCategory.name)
    )
    return result.scalars().all()


async def check_category_has_documents(db: AsyncSession, category_id: UUID) -> bool:
    """检查分类是否包含文档"""
    result = await db.execute(
        select(func.count(PersonalDocument.id))
        .where(PersonalDocument.category_id == category_id)
    )
    count = result.scalar()
    return count > 0


async def check_category_has_children(db: AsyncSession, category_id: UUID) -> bool:
    """检查分类是否有子分类"""
    result = await db.execute(
        select(func.count(DocumentCategory.id))
        .where(DocumentCategory.parent_id == category_id)
    )
    count = result.scalar()
    return count > 0
